mysql 主从同步问题集

        在InnoDB引擎下发现，Mysql的主从热备存在数据不一致的问题，一些数据没有成功同步到备机。在use databases后，更新的表必须是当前选择的database才同步。譬如连上Mysql服务后操作： 

1
2
USE test2; 
UPDATE client SET name='test' WHERE uid=1;

        数据未能同步到备机，而使用use test后，才可以成功同步，如下方式： 

1
2
USE test; 
UPDATE client SET name='test' WHERE uid=1;

        仔细看Mysql手册，发现同步启动选项中还有玄机，只设置replicate-do-db指定同步数据库还不够，是没有设置replicate-wild-do-table导致的跨库同步问题。Mysql默认是同步指定数据库下的更新操作，若要跨库操作更新同步，就必须指定replicate-wild-do-table参数。下面是Mysql手册中对replicate-do-db和replicate-wild-do-table启动选项的说明：

–replicate-do-db=db_name
告诉slave只同步那些缺省数据库是 db_name (也就是用 USE 选中的)的语句。想要指定更多的数据库，只需多次使用该选项，每次指定一个数据库。注意，类似 UPDATE some_db.some_table SET foo=’bar’ 这样的跨库操作语句以及没有选中数据库的操作都不会被同步。如果必须使用跨库操作，要确保使用MySQL 3.23.28或更高，并且使用 –replicate-wild-do-table=db_name.% 选项。请仔细阅读最后面的注意事项。

        下面是一个不能按照预期工作的例子：如果启动slave时使用 –replicate-do-db=sales 选项，并且在master上执行下列语句，那么这个 UPDATE 语句不会被同步： 

1
2
USE prices; 
UPDATE sales.january SET amount=amount+1000;

        如果需要同步跨库操作，只需使用 –replicate-wild-do-table=db_name.% 选项。这个”只检查缺省数据库”特性的主要原因是因为想要单从一个语句中判断是否要被同步比较困难(例如，使用多表 DELETE 或者 UPDATE，这就跨库了)。不过想要检查是否是缺省数据库却很快。

–replicate-wild-do-table=db_name.tblname
限制slave只同步那些匹配指定模式的数据表。模式中可以包含通配符 % 和 ``，它们的含义和 LIKE 模式一样。想要指定更多的数据表，只需多次使用该选项，每次指定一个数据表。请仔细阅读最后面的注意事项。

        例如： –replicate-wild-do-table=foo%.bar% 会同步所有以 foo 开头的数据库下的以 bar 开头的数据表上的更新操作。 如果匹配模式是 %，则匹配所有的表名，且应用到数据库级语句(CREATE DATABASE, DROP DATABASE,和 ALTER DATABASE)。例如，使用 –replicate-wild-do-table=foo%.% 选项的话，所有匹配 foo% 模式的数据库级操作都会被同步。

        如果想要在数据库/表模式中包含原义通配符，需要用反斜杠来转义它们。例如，想要同步 myown%db 数据库下的所有表，但是不想同步 my1ownAABCdb 数据库下的表，就需要转义字符 ``： –replicate-wild-do-table=my\_own\%db。如果是在命令行中使用这个选项，就可能需要两个反斜杠来转义，这依赖于命令行解释器。例如，在 bash shell下，就需要输入： –replicate-wild-do-table=my\\_own\\%db。

        还存在一些问题

        不管有没有replicate-wild-do-table选项，更新操作必须是mysql连接已经有选择的数据库了才进行，譬如新建的mysql(新连接是没有默认选择的database的)连接中执行： 

1
UPDATE test.client SET name='test' WHERE uid=1;

        这条更新无法同步到备机，必须在update前use database操作，该database必须是replicate-wild-do-table中指定的database。

        保险的解决方式：连接上mysql后，调用mysql_select_db()选择数据库，之后进行的更新操作就可以自动同步了。

        SQL模式匹配

        SQL的模式匹配允许你使用“_”匹配任何单个字符，而“%”匹配任意数目字符(包括零个字符)。在MySQL中，SQL的模式缺省是忽略大小写的。下面显示一些例子。注意在你使用SQL模式时，你不能使用=或!=；而使用LIKE或NOT LIKE比较操作符。

        在my.cnf中設定master是錯誤的，你会发现在设置好重启后并没有按照之前的方式来运行。

1
2
3
4
master-host            = 192.168.10.2 
master-user            =  repl 
master-password = slavepass 
master-port            = 3306

        请在mysql下执行底下指令

1
2
3
4
mysql>CHANGE MASTER TO 
MASTER_HOST=’192.168.10.2′, 
MASTER_USER=’repl’, 
MASTER_PASSWORD=’slavepass’;

        查看主从服务状态的指令：

1
2
3
mysql> show slave status\G 
mysql> show master status\G 
mysql> show master logs;

        Mysql主从复制碰到(server_errno=1236)解决过程

        今天上论坛发现新发表的帖子无法正常显示(论坛数据库采用Mysql主从复制进行读写分离方案)，马上想到可能主从复制同步上出现问题，同一时间收到同事消息说数据库的主复制出现故障重启了，这时找到事故原因主数据库重启了会导致从数据库数据复制同步上延后，过一段时间SSH到从数据库上show slave status\G;查看状态显示 

1
2
3
4
5
6
7
 Slave_IO_Running: NO 
 Slave_SQL_Running: Yes 
... 
 Last_Errno: 0 
 Last_Error: 
... 
Seconds_Behind_Master: NULL

        从状态信息来看数据同步没有延后也没有报任何的错误，但Slave_IO_Running: NO显示同步IO进程失败。根据以往经验会先重启一下Slave后在show slave status\G;看一下是否会恢复正常，Slave_IO_Running: NO问题还是没有解决于是查看一下Mysql的错误日志发现： 

1
2
3
4
5
6
090605  9:13:20 [Note] Slave SQL thread initialized, starting replication in log ‘mysql-bin.000102′ at position 1029244974, relay log ‘./xxx-relay-bin.000634′ position: 98 
090605  9:13:20 [Note] Slave I/O thread: connected to master ’slave163@192.168.0.131:3306′,  replication started in log ‘mysql-bin.000102′ at position 1029244974 
090605  9:13:20 [ERROR] Error reading packet from server: Client requested master to start replication from impossible position ( server_errno=1236) 
090605  9:13:20 [ERROR] Got fatal error 1236: ‘Client requested master to start replication from impossible position’ from master when reading data from binary log 
090605  9:13:20 [Note] Slave I/O thread exiting, read up to log ‘mysql-bin.000102′, position 1029244974 
090605  9:13:52 [Note] Error reading relay log event: slave SQL thread was killed

        在网上查了很多资料可能因为mysql-bin.000102日志文件中并没有1029244974这个位置，vi打开mysql-bin.000102日志文件搜索1029244974确实没有搜到内容，但为什么主数据库意外的重启会导致Slave上读取位置的错误，记得以前主数据库也手动重启过，Slave上也没有出现过这种问题。解决方法是使用CHANGE MASTER TO命令就是让Slave跳过mysql-bin.000102日志文件1029244974这个位置直接到下一个日志文件： 

1
2
3
SLAVE STOP; 
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000103', MASTER_LOG_POS=0; 
SLAVE START;

        但是这样如果mysql-bin.000102日志文件的内容没有同步完会导致主数据库与从数据库的内容不一致，那何不把位置向前移呢。 

1
2
3
SLAVE STOP; 
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000102', MASTER_LOG_POS=1019244974; 
SLAVE START;

        于是试了一下，果然Slave_IO_Running: YES同步成功跳过了mysql-bin.000102日志文件1029244974这个位置并继续读取下一个位置，这样从数据库也不会丢失数据和主数据库保持数据的一致，如果my.cnf中没有加slave-skip-errors参数跳过一些错误的话，同步位置向前移会导致一些数据重新插入到表中报主键重复错误加上参数就可以成功跳过这些错误了。

        今天一台数据库的slave报 Slave_IO_Running: No的错误, 登陆上机器执行. 

1
2
3
>slave stop; 
>SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; 
>slave start;

        看看slave的状态, 依然是Slave_IO_Running: No

        看看mater错误日志, 发现有一段奇怪的日志如下: 

1
Got timeout reading communication packets

        看看master的错误日志, 那就更奇怪了: 

1
2
3
4
090430 15:49:38 [Note] Slave I/O thread: connected to master 'user@192.16.0.123:3306',replication started in log 'xxx-bin.000815' at position 3776386 
090430 15:49:38 [ERROR] Error reading packet from server: Client requested master to start replication from impossible position ( server_errno=1236) 
090430 15:49:38 [ERROR] Got fatal error 1236: 'Client requested master to start replication from impossible position' from master when reading data from binary log 
090430 15:49:38 [Note] Slave I/O thread exiting, read up to log 'xxx-bin.000815', position 3776386

        可能是xxx-bin.000815这个文件有问题, 看了一下它的大小，果然没有3776386这个位置，slave读的时候肯定是错误了， 到底为什么会这样就不清楚了。

        解决办法就是读取下一个bin-log了 

1
2
3
4
>slave stop; 
>CHANGE MASTER TO MASTER_LOG_FILE='xxx-bin.000816',MASTER_LOG_POS=0; 
>slave start; 
>show slave status\G;

        可以看到Slave_IO_Running: Yes, 问题解决.

        从库配置文件my2.ini 

1
2
3
port=3307 
datadir=”” 
server-id=2

        启用从库日志，这样可以进行链式复制 

1
log-slave-updates

        从库是否只读，0表示可读写，1表示只读 

1
read-only=1

        只复制某个表 

1
replicate-do-table=tablename

        只复制某些表(可用匹配符) 

1
replicate-wild-do-table=tablename%

        只复制某个库 

1
replicate-do-db=dbname

        只复制某些库 

1
replicte-wild-do-db=dbname%

        不复制某个表 

1
replicate-ignore-table=tablename

        不复制某些表 

1
replicate-wild-ignore-table=tablename%

        不复制某个库 

1
replicate-ignore-db=dbname

        复制完的sql语句是否立即从中继日志中清除，1表示立即清除 

1
relay-log-purge=1

        从服务器主机，用于show slave hosts生成从库清单 

1
report-host=hostname

        在从库的数据目录下，有几个和复制相关的文件需要说明一下：

        *-reloay-bin.* 从主库同步过来的Bin log文件，也叫中继日志

        master.info 主库帐号信息和同步信息，这里记录了复制用户名和密码，需要保护好权限。

        relay-log.info 跟踪执行同步过来的Bin log的执行情况

        通过show processlist可以查看主从库用于复制的相关进程(在windows上实际实现为线程)的信息 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
mysql> show processlist\G 
mysql> CHANGE MASTER TO 
 -> MASTER_HOST='master_host_name', 
 -> MASTER_USER='replication_user_name', 
 -> MASTER_PASSWORD='replication_password', 
 -> MASTER_LOG_FILE='recorded_log_file_name', 
 -> MASTER_LOG_POS=recorded_log_position; 
```          
        注意：从服务器复制时，会在其数据目录中发现文件master.info和HOSTNAME-relay-log.info。状态文件保存在硬盘上，从服务器关闭时不会丢失。下次从服务器启动时，读取这些文件以确定它已经从主服务器读取了多少二进制日志，以及处理自己的中继日志的程度。不要移除或编辑这些文件，除非你确切知你正在做什么并完全理解其意义。即使这样，最好是使用CHANGE MASTER TO语句。 
        为了保证以后binglog及时写入，将主库sync_binlog变量设置1。 
----
        MYSQL主从同步时出现”Client requested master to start replication from impossible position”错误的解决方法: 
        这个错误是因为从服务器请求一个错误的位置而引起的.比如主服务器上的BLIN LOG里没有这个POSITION. 
        解决原理: 
        从相关的BIN LOG 里最后一个日志位置,与从服务器上的日志对比,如果LOG里最后位置比错误的位置大,那么,说明中间可能有断点,需要把从服务器的位置向前调,多试几次.如果在LOG里最后的位置比从服务器指出的位置还小,那么只要把从服务器的位置设置成LOG的最后位置就可以了. 
```bash
mysqlbinlog mysql-log-bin.000112 >log.sql 
tail -f log.sql 
# at 568380594 
#110616  3:10:16 server id 1  end_log_pos 568380757     Query   thread_id=123899        exec_time=0     error_code=0 
use thecheap_topshoppinguscom/*!*/; 
SET TIMESTAMP=1308226216/*!*/; 
DELETE FROM `thecheap_topshoppinguscom`.`zm_sessions` WHERE expiry < 1308224416 
/*!*/; 
DELIMITER ; 
# End of log file 
ROLLBACK /* added by mysqlbinlog */; 
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

        发现这个位置比从服务器上的位置(568397810)还小,只要设置成最后位置,然后启动SLAVE就可以了. 

1
2
3
CHANGE MASTER TO 
MASTER_LOG_FILE=’mysql-log-bin.000112′, 
MASTER_LOG_POS=568380594;

–read-only选项
该选项让从服务器只允许来自从服务器线程或具有SUPER权限的用户的更新，可以确保从服务器不接受来自客户的更新。在测试过程中遇到了read-only的问题，发现写操作因为read-only这个选项的开启，而不能够成功执行。而通过带super权限的用户执行带修改性质的语句时，是能成功执行的。查找了下资料，才了解read-only的真正含义和用法：
–read_only Make all non-temporary tables read-only, with the exception for replication (slave) threads and users with the SUPER privilege.

        SUPER privilege :

        The SUPER privilege enables an account to use CHANGE MASTER TO , KILL or mysqladmin kill to kill threads belonging to other accounts (you can always kill your own threads), PURGE BINARY LOGS , configuration changes using SET GLOBAL to modify global system variables, the mysqladmin debug command, enabling or disabling logging, performing updates even if the read_only system variable is enabled, starting and stopping replication on slave servers, specification of any account in the DEFINER attribute of stored programs and views, and enables you to connect (once) even if the connection limit controlled by the max_connections system variable is reached.

        To create or alter stored routines if binary logging is enabled, you may also need the SUPER privilege, as described in Section 18.6, “Binary Logging of Stored Programs” .

        read-only选项：对所有的非临时表进行只读控制。但是有两种情况例外：

  1. 对replication threads例外，以保证slave能够正常的进行replication。
  2. 对于拥有super权限的用户，可以ignore这个选项。

        SUPER 权限 ：

  1. 可以有change master to, kill其他用户的线程的权限。
  2. Purge binary logs 来删除binary log, set global来动态设置变量的权限。
  3. 执行mysqladmin debug命令，开启或者关闭log，在read-only打开时执行update/insert操作。
  4. 执行start slave, stop slave.
  5. 当连接数已经达到max_connections的最大值时，也可以连接到server。

        mysql 主从同步状态不一致问题

        方法一：是强制性从某一个点开始同步，会有部分没有同步的数据丢失，后续主服务器上删除记录同步也会有一些错误信息，不会影响使用。

        方法二：是设置’set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;’，但这样做不一定会有效果。

        主从不能同步:

1
show slave status;

        报错:Error xxx dosn’t exist

1
2
3
show slave status\G:
Slave_SQL_Running: NO
Seconds_Behind_Master: NULL

        解决方法:

1
2
3
stop slave;
set global sql_slave_skip_counter =1 ;
start slave;

        之后Slave会和Master去同步 主要看:

1
2
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

        Seconds_Behind_Master是否为0，0就是已经同步了

        还需要做的一些优化与监视:

1
2
3
4
show full processlist; //查看mysql当前同步线程号
skip-name-resolve       //跳过dns名称查询，有助于加快连接及同步的速度
max_connections=1000    //增大Mysql的连接数目，(默认100)
max_connect_errors=100 //增大Mysql的错误连接数目,(默认10)

        查看日志一些命令

  1. show master status\G;
    在这里主要是看log-bin的文件是否相同。
1
show slave status\G;

        在这里主要是看:

1
2
Slave_IO_Running=Yes
Slave_SQL_Running=Yes

        如果都是Yes,则说明配置成功.

  1. 在master上输入show processlist\G;
1
2
3
4
5
6
7
8
9
10
mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
Id: 2
User: root
Host: localhost:32923
db: NULL
Command: Binlog Dump
Time: 94
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL

        如果出现Command: Binlog Dump,则说明配置成功.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
stop slave    #停止同步
start slave    #开始同步，从日志终止的位置开始更新。
SET SQL_LOG_BIN=0|1  #主机端运行，需要super权限，用来开停日志，随意开停，会造成主机从机数据不一致，造成错误
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=n  # 客户端运行，用来跳过几个事件，只有当同步进程出现错误而停止的时候才可以执行。
RESET MASTER  #主机端运行,清除所有的日志，这条命令就是原来的FLUSH MASTER
RESET SLAVE   #从机运行，清除日志同步位置标志，并重新生成master.info
虽然重新生成了master.info,但是并不起用，最好，将从机的mysql进程重启一下，
LOAD TABLE tblname FROM MASTER #从机运行，从主机端重读指定的表的数据，每次只能读取一个，受timeout时间限制，需要调整timeout时间。执行这个命令需要同步账号有 reload和super权限。以及对相应的库有select权限。如果表比较大，要增加net_read_timeout 和 net_write_timeout的值
LOAD DATA FROM MASTER  #从机执行，从主机端重新读入所有的数据。执行这个命令需要同步账号有reload和super权限。以及对相应的库有select权限。如果表比较大，要增加net_read_timeout 和 net_write_timeout的值
CHANGE MASTER TO master_def_list  #在线改变一些主机设置，多个用逗号间隔,比如
CHANGE MASTER TO
  MASTER_HOST='master2.mysql.com',
  MASTER_USER='replication',
  MASTER_PASSWORD='password'
MASTER_POS_WAIT() #从机运行
SHOW MASTER STATUS #主机运行，看日志导出信息
SHOW SLAVE HOSTS #主机运行，看连入的从机的情况。
SHOW SLAVE STATUS (slave)
SHOW MASTER LOGS (master)
SHOW BINLOG EVENTS [ IN 'logname' ] [ FROM pos ] [ LIMIT [offset,] rows ]
PURGE [MASTER] LOGS TO 'logname' ; PURGE [MASTER] LOGS BEFORE 'date'

        清理binlog日志文件

        发现放数据库的分区磁盘激增了40多G，一路查看下来，发现配置好主从复制以来到现在的binlog就有40多G，原来根源出在这里，查看了一下 my.cnf，看到binlog的size是1G就做分割，但没有看到删除的配置，在mysql里show了一下variables，

1
mysql> show variables like '%log%';

        查到了

1
| expire_logs_days                | 0                                     |

        这个默认是0，也就是logs不过期，这个是一个global的参数，所以需要执行

1
set global expire_logs_days=8;

        这样8天前的log就会被删除了，如果有回复的需要，请做好备份工作，但这样设置还不行，下次重启mysql了，配置又恢复默认了，所以需在my.cnf中设置：

1
expire_logs_days = 8

        想要恢愎数据库以前的资料，执行：show binlog events;

        由于数据量很多，查看起来很麻烦，所以应该适当删除部分可不用的日志。并且如果使用的时间足够长的话，会把我的硬盘空间都给吃掉。

  1. 登录系统，/usr/bin/mysql

        使用mysql查看日志

1
2
3
4
5
6
7
8
mysql> show binary logs;
+—————-+———–+
| Log_name         | File_size |
+—————-+———–+
| mysql-bin.000001 | 150462942 |
| mysql-bin.000002 | 120332942 |
| mysql-bin.000003 | 141462942 |
+—————-+———–+
  1. 删除bin-log(删除mysql-bin.000003之前的而没有包含mysql-bin.000003)
1
2
3
mysql> purge binary logs to ‘mysql-bin.000003′;
Query OK, 0 rows affected (0.16 sec)
  1. 查询结果(现在只有一条记录了.)
1
2
3
4
5
6
7
8
9
10
mysql> show binlog events\G
mysql> show binary logs;
+—————-+———–+
| Log_name       | File_size |
+—————-+———–+
| mysql-bin.000003 |       106 |
+—————-+———–+
1 row in set (0.00 sec)

        (删除的其它格式运用!)

1
2
PURGE {MASTER | BINARY} LOGS TO ‘log_name’
PURGE {MASTER | BINARY} LOGS BEFORE ‘date’

        用于删除列于在指定的日志或日期之前的日志索引中的所有二进制日志，这些日志也会从记录在日志索引文件中的清单中被删除，这样被给定的日志成为第一个。例如：

1
2
PURGE MASTER LOGS TO ‘mysql-bin.010′;
PURGE MASTER LOGS BEFORE ‘2008-06-22 13:00:00′;

        清除3天前的 binlog

1
PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY);

        BEFORE变量的date自变量可以为’YYYY-MM-DD hh:mm:ss’格式。MASTER和BINARY是同义词。如果您有一个活性的从属服务器，该服务器当前正在读取您正在试图删除的日志之一，则本语句不会起作用，而是会失败，并伴随一个错误。不过，如果从属服务器是休止的，并且您碰巧清理了其想要读取的日志之一，则从属服务器启动后不能复制。当从属服务器正在复制时，本语句可以安全运行。您不需要停止它们。

        要清理日志，需按照以下步骤：

  1. 在每个从属服务器上，使用SHOW SLAVE STATUS来检查它正在读取哪个日志。
  2. 使用SHOW MASTER LOGS获得主服务器上的一系列日志。
  3. 在所有的从属服务器中判定最早的日志。这个是目标日志。如果所有的从属服务器是更新的，这是清单上的最后一个日志。
  4. 制作您将要删除的所有日志的备份。
  5. 清理所有的日志，但是不包括目标日志。

        下面讲一下怎么从二进制文件恢复数据, 假如不小心执行了drop table xxx_db, 假如你保留了完整的二进制日志的话, 先不要冒汗, 这是可以恢复的。

        先看看日志

1
>mysqlbinlog /diskb/bin-logs/xxx_db-bin.000001

        找到执行create table xxx_db之后和drop table xxx_db之前的position, 假如是20, 1000.

1
>mysqlbinlog --start-position="4" --stop-position="1000" /diskb/bin-logs/xxx_db-bin.000001 | mysql -u root

        还有一种办法是根据日期来恢复

1
>mysqlbinlog --start-datetime="2010-09-14 0:20:00" --stop-datetim="2010-09-15 01:25:00" /diskb/bin-logs/xxx_db-bin.000001 | mysql -u root

        如果相关的语句不在同一个binlog文件里，则需要从不同的文件来恢复。

        如果MySQL服务器上有多个要执行的二进制日志，安全的方法是在一个连接中处理它们。下面是一个说明什么是不安全的例子：

1
2
shell> mysqlbinlog hostname-bin.000001 | mysql # DANGER!!
shell> mysqlbinlog hostname-bin.000002 | mysql # DANGER!!

        使用与服务器的不同连接来处理二进制日志时，如果第1个日志文件包含一个CREATE TEMPORARY TABLE语句，第2个日志包含一个使用该临时表的语句，则会造成问题。当第1个mysql进程结束时，服务器撤销临时表。当第2个mysql进程想使用该表时，服务器报告 “不知道该表”。

        要想避免此类问题，使用一个连接来执行想要处理的所有二进制日志中的内容。下面提供了一种方法：

1
shell> mysqlbinlog hostname-bin.000001 hostname-bin.000002 | mysql

        或：

1
2
3
shell> mysqlbinlog hostname-bin.000001 >  /tmp/statements.sql
shell> mysqlbinlog hostname-bin.000002 >> /tmp/statements.sql
shell> mysql -e "source /tmp/statements.sql"

        mysql主从重新同步’binlog’日志

        binlog中有一个位置(position)变量，可用于控制其工作进程。

  1. 从’slave’在某个’position’之后停止同步
1
START SLAVE UNTIL MASTER_LOG_FILE='xxxxx', MASTER_LOG_POS=yyyyyy;

        注：在执行前要确定从库的同步已停。

  1. 重新从某一’position’后同步数据

        笔者所碰到的一个现象：在一台繁忙的主库上，其传输过来的语句在从库没有完全得到执行，从而导致一分多钟的数据丢失(这么多年第一次碰到，事后通过分析binlog日志文件才发现，从库的状态居然是正常的)。
STOP SLAVE;

1
2
change master to master_host='master',master_user='user',master_password='passwd',master_log_file='mysql-bin.file',master_log_pos=prev_position;
START SLAVE;

        这样可以从出问题的时间点靠前一点的’position’开始重新同步’sql‘操作。但这样会报错，因为之前有数据存在了。诸如此类：

1
2
Last-Errno: 1062
Last-Error: Error 'Duplicate entry '15386' for key 1' on query. Default database: 'db'. Query: 'INSERT INTO db.table ( FIELDS ) VALUES ( VALUES )'

        键重复了，必须跳过才能继续。

1
stop slave; set global sql_slave_skip_counter=1; start slave;

        或者在’my.cnf’文件中指定该错误跳过，

1
--slave-skip-errors=xxx,yyy

        须重启服务器使其生效。常见问题及操作：

1
2
3
4
5
Error: 1062 SQLSTATE: 23000 (ER_DUP_ENTRY) Message: Duplicate entry ‘%s’ for key %d
You can skip also other type of errors, but again don’t do this unless you understand very well what those queries are and what impact they have on your data:
slave-skip-errors=[err_code1,err_code2,...|all]

        报错相关的更多信息，请参考：http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html

        注：如果’xxx’为’all’的话，则表示跳过所有错误并继续，但这并不是个好的建议。这就很必要将从库设定为只读(read_only)且用非特权用户来访问它。

        官方文档有关于它的(replication-slave)更多参考：http://dev.mysql.com/doc/refman/5.0/en/replication-options-slave.html

        Q. No argument was provided to –log-bin, and –log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use ‘–log-bin=mysql-bin’ to avoid this problem.

        A: 在my.cnf 的[mysqld]中加入 log-bin=mysql-bin

        Q. Neither –relay-log nor –relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use ‘–relay-log=mysql-relay-bin’ to avoid this problem.

        A: 在my.cnf 的[mysqld]中加入 relay-log=mysql-relay-bin

        Q. [ERROR] The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the –replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

        A: 在my.cnf 的[mysqld]中加入 replicate-same-server-id

        Q.[Note] Slave I/O thread: connected to master ‘test@:3306′,replication started in log ‘FIRST’ at position 4

        [ERROR] Error reading packet from server: Access denied; you need the REPLICATION SLAVE privilege for this operation ( server_errno=1227)

        A: 可能原来使用过slave链接.需要将mysql库中的master.info删除重新

        Q：如果主服务器正在运行并且不想停止主服务器，怎样配置一个从服务器？

        A：有多种方法。如果你在某时间点做过主服务器备份并且记录了相应快照的二进制日志名和偏移量(通过SHOW MASTER STATUS命令的输出)，采用下面的步骤：

  1. 确保从服务器分配了一个唯一的服务器ID号。
  2. 在从服务器上执行下面的语句，为每个选项填入适当的值： 
1
2
3
4
5
6
mysql> CHANGE MASTER TO 
 ->MASTER_HOST='master_host_name', 
 ->MASTER_USER='master_user_name', 
 ->MASTER_PASSWORD='master_pass', 
 ->MASTER_LOG_FILE='recorded_log_file_name', 
 ->MASTER_LOG_POS=recorded_log_position;
  1. 在从服务器上执行START SLAVE语句。

        如果你没有备份主服务器，这里是一个创建备份的快速程序。所有步骤都应该在主服务器主机上执行。

  1. 发出该语句： 
1
mysql> FLUSH TABLES WITH READ LOCK；
  1. 仍然加锁时，执行该命令(或它的变体)： 
1
shell> tar zcf /tmp/backup.tar.gz /var/lib/mysql
  1. 发出该语句并且确保记录了以后用到的输出： 
1
mysql>SHOW MASTER STATUS；
  1. 释放锁： 
1
mysql> UNLOCK TABLES；

        一个可选择的方法是，转储主服务器的SQL来代替前面步骤中的二进制复制。要这样做，你可以在主服务器上使用mysqldump –master-data，以后装载SQL转储到到你的从服务器。然而，这比进行二进制复制速度慢。

        不管你使用这两种方法中的那一个，当你有一个快照和记录了日志名与偏移量时，后来根据说明操作。你可以使用相同的快照建立多个从服务器。一旦你拥有主服务器的一个快照，可以等待创建一个从服务器，只要主服务器的二进制日志完整。两个能够等待的时间实际的限制是指在主服务器上保存二进制日志的可用硬盘空间和从服务器同步所用的时间。

        你也可以使用LOAD DATA FROM MASTER。这是一个方便的语句，它传输一个快照到从服务器并且立即调整日志名和偏移量。将来，LOAD DATA FROM MASTER将成为创建从服务器的推荐方法。然而需要注意，它只工作在MyISAM 表上并且可能长时间持有读锁定。它并不象我们希望的那样高效率地执行。如果你有大表，执行FLUSH TABLES WITH READ LOCK语句后，这时首选方法仍然是在主服务器上制作二进制快照。

        Q：从服务器需要始终连接到主服务器吗？

        A：不，不需要。从服务器可以宕机或断开连接几个小时甚至几天，重新连接后获得更新信息。例如，你可以在通过拨号的链接上设置主服务器/从服务器关系，其中只是偶尔短时间内进行连接。这意味着，在任何给定时间，从服务器不能保证与主服务器同步除非你执行某些特殊的方法。将来，我们将使用选项来阻塞主服务器直到有一个从服务器同步。

        log-slave-updates #这个参数一定要加上，否则不会给更新的记录些到二进制文件里

        slave-skip-errors #是跳过错误，继续执行复制操作

        主服务器上的相关命令：

1
2
3
4
5
6
7
8
show master status
show slave hosts
show logs
show binlog events
purge logs to 'log_name'
purge logs before 'date'
reset master(老版本flush master)
set sql_log_bin=

        从服务器上的相关命令:

1
2
3
4
5
6
7
8
9
10
11
12
13
slave start
slave stop
SLAVE STOP IO_THREAD //此线程把master段的日志写到本地
SLAVE start IO_THREAD
SLAVE STOP SQL_THREAD //此线程把写到本地的日志应用于数据库
SLAVE start SQL_THREAD
reset slave
SET GLOBAL SQL_SLAVE_SKIP_COUNTER
load data from master
show slave status(SUPER,REPLICATION CLIENT)
CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,MASTER_USER=, MASTER_PASSWORD= //动态改变master信息
PURGE MASTER [before 'date'] 删除master端已同步过的日志
6.3.1 Master 同步线程状态

        以下列出了master的 Binlog Dump 线程 State 字段中最常见的几种状态。如果在master上没有 Binlog Dump 线程，那么同步就没有在运行。

        也就是说，没有slave连接上来。

        Sending binlog event to slave

        事件是由二进制日志构成，一个事件通常由更新语句加上其他信息。线程读取到一个事件并正发送到slave上。

        Finished reading one binlog; switching to next binlog

        读取完了一个二进制日志，正切换到下一个。

        Has sent all binlog to slave; waiting for binlog to be updated

        已经读取完全部未完成更新日志，并且全部都发送到slave了。它处于空闲状态，正等待在master上执行新的更新操作以在二进制日志中产生新
的事件，然后读取它们。

1
Waiting to finalize termination

        当前线程停止了，这个时间很短。

        6.3.2 Slave的I/O线程状态

        以下列出了slave的I/O线程 State 字段中最常见的几种状态。从MySQL 4.1.1开始，这个状态在执行 SHOW SLAVE STATUS 语句结果的

        Slave_IO_State 字段也会出现。这意味着可以只执行 SHOW SLAVE STATUS 语句就能了解到更多的信息。

Connecting to master

        该线程证尝试连接到master上。

Checking master version

确定连接到master后出现的一个短暂的状态。

Registering slave on master

确定连接到master后出现的一个短暂的状态。

Requesting binlog dump

确定连接到master后出现的一个短暂的状态。该线程向master发送一个请求，告诉它要请求的二进制文件以及开始位置。

Waiting to reconnect after a failed binlog dump request

        如果二进制日志转储(binary log dump)请求失败了(由于连接断开)，该线程在休眠时进入这个状态，并定期重连。重连的时间间隔由

        –master-connect-retry 选项来指定。

        Reconnecting after a failed binlog dump request

        该线程正尝试重连到master。

        Waiting for master to send event

        已经连接到master，正等待它发送二进制日志。如果master闲置时，这个状态可能会持续较长时间，如果它等待超过 slave_read_timeout 秒
，就会发生超时。这时，它就会考虑断开连接，然后尝试重连。

        Queueing master event to the relay log

        已经读取到一个事件，正把它拷贝到中继日志中以备SQL线程处理。

        Waiting to reconnect after a failed master event read

        读日志时发生错误(由于连接断开)。该线程在重连之前休眠 master-connect-retry 秒。

        Reconnecting after a failed master event read

        正尝试重连到master。当连接确定后，状态就变成 Waiting for master to send event。

        Waiting for the slave SQL thread to free enough relay log space

        relay_log_space_limit 的值非零，中继日志的大小总和超过这个值了。I/O线程等待SQL线程先处理中继日志然后删除它们以释放足够的空间
。

        Waiting for slave mutex on exit

        当前线程停止了，这个时间很短。

        6.3.3 Slave的SQL线程状态

        以下列出了slave的SQL线程 State 字段中最常见的几种状态：

        Reading event from the relay log

        从中继日志里读到一个事件以备执行。

        Has read all relay log; waiting for the slave I/O thread to update it

        已经处理完中继日志中的全部事件了，正等待I/O线程写入更新的日志。

        Waiting for slave mutex on exit

        当前线程停止了，这个时间很短。

        常见的一些问题：

        一.从库SLAVE启动问题

        由于一些错误操作导致 CHANGE MASTER 和 SLAVE 服务无法启动，系统报错如下：

1
2
3
*****************************************************************
Could not initialize master info structure; more error messages can be found in the MySQL error log.
*****************************************************************

        无法初始化master info结构，MySQL错误日志记录了更详细的错误信息。

        解决方法：

  1. 查看MySQL错误日志，如：同步的上一个Position是多少，很多情况下无法启动服务是由于mysql识别的同步始终停留在上一个Position上。
  2. 查看master.info和relay-log.info，master.info 记录MASTER相关信息，relay-log.info 记录当前同步日志信息。
  3. 停止myslq服务，删除master.info和relay-log.info。
  4. 启动mysql服务。
  5. 重新CHANGE MASTER，重新启动SLAVE服务。

        二.主从不能同步

        show slave status;报错:Error xxx dosn’t exist,且

1
2
3
show slave status\G:
Slave_SQL_Running: NO
Seconds_Behind_Master: NULL

        解决方法:

1
2
3
stop slave;
set global sql_slave_skip_counter =1 ;
start slave;

        之后Slave会和Master去同步 主要看:

1
2
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

        Seconds_Behind_Master是否为0，0就是已经同步了

  1. 还需要做的一些优化与监视:
1
2
3
4
show full processlist; //查看mysql当前同步线程号
skip-name-resolve       //跳过dns名称查询，有助于加快连接及同步的速度
max_connections=1000    //增大Mysql的连接数目，(默认100)
max_connect_errors=100 //增大Mysql的错误连接数目,(默认10)

        查看日志一些命令

  1. show master status\G;

        在这里主要是看log-bin的文件是否相同。

1
show slave status\G;

        在这里主要是看:

1
2
Slave_IO_Running=Yes
Slave_SQL_Running=Yes

        如果都是Yes,则说明配置成功.

  1. 在master上输入show processlist\G;
1
mysql> SHOW PROCESSLIST\G

        如果出现Command: Binlog Dump,则说明配置成功.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
stop slave    #停止同步
start slave    #开始同步，从日志终止的位置开始更新。
SET SQL_LOG_BIN=0|1  #主机端运行，需要super权限，用来开停日志，随意开停，会造成主机从机数据不一致，造成错误
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=n  # 客户端运行，用来跳过几个事件，只有当同步进程出现错误而停止的时候才可以执行。
RESET MASTER  #主机端运行,清除所有的日志，这条命令就是原来的FLUSH MASTER
RESET SLAVE   #从机运行，清除日志同步位置标志，并重新生成master.info
虽然重新生成了master.info,但是并不起用，最好，将从机的mysql进程重启一下，
LOAD TABLE tblname FROM MASTER #从机运行，从主机端重读指定的表的数据，每次只能读取一个，受timeout时间限制，需要调整timeout时间。执行这个命令需要同步账号有 reload和super权限。以及对相应的库有select权限。如果表比较大，要增加net_read_timeout 和 net_write_timeout的值
LOAD DATA FROM MASTER  #从机执行，从主机端重新读入所有的数据。执行这个命令需要同步账号有reload和super权限。以及对相应的库有select权限。如果表比较大，要增加net_read_timeout 和 net_write_timeout的值
CHANGE MASTER TO master_def_list  #在线改变一些主机设置，多个用逗号间隔，比如：
CHANGE MASTER TO
MASTER_HOST='master2.mycompany.com',
MASTER_USER='replication',
MASTER_PASSWORD='bigs3cret'
MASTER_POS_WAIT() #从机运行
SHOW MASTER STATUS #主机运行，看日志导出信息
SHOW SLAVE HOSTS #主机运行，看连入的从机的情况。
SHOW SLAVE STATUS (slave)
SHOW MASTER LOGS (master)
SHOW BINLOG EVENTS [ IN 'logname' ] [ FROM pos ] [ LIMIT [offset,] rows ]
PURGE [MASTER] LOGS TO 'logname' ; PURGE [MASTER] LOGS BEFORE 'date'
show binlog events; #查看主库二进制日志文件内容：

        注意：

  1. 主辅库同步主要是通过二进制日志来实现同步的。

  2. 在启动辅库的时候必须先把数据同步，并删除日志目录下的：master.info文件。因为master.info记录了上次要连接主库的信息，如果不删除，即使my.cnf里进行了修改，也不起作用，因为读取的还是master.info文件里的信息。

        补充：从服务器上my.cnf中的master-*的设置仅在第一次生效，后保存在master.info文件里。

        在从服务器上使用show slave status

        Slave_IO_Running,为No,则说明IO_THREAD没有启动，请执行slave start [IO_THREAD]

        Slave_SQL_Running为No则复制出错,查看Last_error字段排除错误后执行slave start [SQL_THREAD]

        查看Slave_IO_State字段

        空 //复制没有启动

        Connecting to master//没有连接上master

        Waiting for master to send event//已经连上

        可以使用LOAD DATA FROM MASTER语句来建立slave，但有约束条件：

        数据表要全部是MyISAM表，必须有SUPER权限，master的复制用户必须具备RELOAD和SUPER权限。

        在master端执行RESET MASTER清除已有的日志变更，此时slave端会因为找不到master日志无法启动IO_THREAD，请清空data目录下

        relay-log.info,hosname-relay-bin*等文件重新启动mysql

        中继日志文件默认的文件为hostname-relay-bin.nnn和hostname-relay-bin.index。可用从服务器的–

        relay-log和–relay-log-index选项修改。在从服务器中还有一个relay-log.info中继信息文件，可用

        –relay-log-info-file启动选项修改文件名。双机互备则是两个mysql同时配置为master及slave

        主服务器上的相关命令：

1
2
3
4
5
6
7
8
show master status
show slave hosts
show {master|binary} logs
show binlog events
purge {master|binary} logs to 'log_name'
purge {master|binary} logs before 'date'
reset master(老版本flush master)
set sql_log_bin={0|1}

        从服务器上的相关命令:

1
2
3
4
5
6
7
8
9
10
11
12
slave start
slave stop
SLAVE STOP IO_THREAD //此线程把master段的日志写到本地
SLAVE start IO_THREAD
SLAVE STOP SQL_THREAD //此线程把写到本地的日志应用于数据库
SLAVE start SQL_THREAD
reset slave
SET GLOBAL SQL_SLAVE_SKIP_COUNTER
load data from master
show slave status(SUPER,REPLICATION CLIENT)
CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,MASTER_USER=, MASTER_PASSWORD= //动态改变master信息
PURGE MASTER [before 'date'] 删除master端已同步过的日志

        Mysql的Relay Log无法自动删除

        发现其数据目录下的relay-log 长期没有删除，已经堆积了几十个relay-log。然而其他作为Slave服务器实例却没有这种情况，综合分析后发现和以下原因有关。

  • 该实例原先是一个Slave ——-导致relay-log 和 relay-log.index的存在
  • 该实例目前已经不是Slave ——-由于没有了IO-Thread，导致relay-log-purge 没有起作用( 这也是其他Slave实例没有这种情况的原因，因为IO-thread会做自动rotate操作)。
  • 该实例每天会进行日常备份 ——-Flush logs的存在，导致每天会生成一个relay-log
  • 该实例没有配置expire-logs-days ——导致flush logs时，也不会做relay-log清除

        简而言之就是： 一个实例如果之前是Slave，而之后停用了(stop slave)，且没有配置expire-logs-days的情况下，会出现relay-log堆积的情况。

        顺带也和大家分享下MySQL内部Logrotate的机制

        Binary Log rotate机制：

  • Rotate：每一条binary log写入完成后，都会判断当前文件是否超过 max_binlog_size，如果超过则自动生成一个binlog file。
  • Delete：expire-logs-days 只在 实例启动时 和 flush logs 时判断，如果文件访问时间早于设定值，则purge file。

        Relay Log rotate 机制：

  • Rotate：每从Master fetch一个events后，判断当前文件是否超过 max_relay_log_size 如果超过则自动生成一个新的relay-log-file。
  • Delete：purge-relay-log 在SQL Thread每执行完一个events时判断，如果该relay-log 已经不再需要则自动删除。
  • Delete：expire-logs-days 只在 实例启动时 和 flush logs 时判断，如果文件访问时间早于设定值，则purge file (同Binlog file) (注意: expire-logs-days和relaylog的purge没有关系)。

        因此建议当slave不再使用时，通过reset slave来取消relaylog，以免出现relay-log堆积的情况。

s        lave的主从信息变更引起的错误

1
2
3
4
5
6
7
8
130311 14:15:46  mysqld started
130311 14:15:46 [Warning] option 'read_rnd_buffer_size': unsigned value 0 adjusted to 8200
130311 14:15:47  InnoDB: Started; log sequence number 15 2381115047
130311 14:15:47 [ERROR] Failed to open the relay log './localhost-relay-bin.000037' (relay_log_pos 52474065)
130311 14:15:47 [ERROR] Could not find target log during relay log initialization
130311 14:15:47 [ERROR] Failed to initialize the master info structure
130311 14:15:47 [Note] /usr/local/mysql/libexec/mysqld: ready for connections.
Version: '5.0.87-log'  socket: '/var/tmp/mysql2.sock'  port: 3308  Source distribution

        由于新的slave改变了服务端口和文件路径，分析应该是由于mysql-relay-bin.index中仍然保存着旧relay日志文件的路径，而这些路径下又找不到合适的文件，因此报错。

        对于这类问题解决起来是比较简单的，重置slave的参照即可。

1
2
3
4
5
mysql> reset slave;
Query OK, 0 rows affected (0.01 sec)
mysql> change master to ....
ERROR 29 (HY000): File '/data/mysqldata/3306/binlog/mysql-relay-bin.000001' not found (Errcode: 2)

        看来应该还是mysql-relay-bin.index的问题，删除该文件及关联的relay-bin文件。再次配置master：

1
2
mysql> change master to ....
ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log

        出现了新的错误，按照提示查看error_log也没发现更多错误信息，error_log中只是显示一条：

1
120326 11:14:27 [ERROR] Error reading master configuration

        在操作系统端查看master/slave的配置文件，发现是两个0字节文件：

1
2
-rw-rw---- 1 mysql mysql     0 Mar 26 11:13 master.info
-rw-rw---- 1 mysql mysql     0 Mar 26 11:13 relay-log.info

        会不会是这个原因呢，直接删除这两个文件，然后尝试重新执行change master：

1
2
mysql> change master to ....
Query OK, 0 rows affected (0.00 sec)

        成功，启动slave并查看状态：

1
2
3
4
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G

        故障解决。

        磁盘故障

1
Warning: a page in the doublewrite buffer is not within space

        出现此种报错，多为磁盘故障。

        master不能初始化

        ERROR 1201 (HY000): Could not initialize master解决方法 

1
2
3
4
5
6
7
mysql> change master to master_host='192.168.1.10',master_user='replication',master_password='123456',master_log_file='freeoa_log.000003',master_log_pos=106;
ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log
mysql> slave stop;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> reset slave;

        出现这个问题的原因，应该是以前mysql做过主从。